/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apus.persistence.jdbc;

import apus.entity.Organization;
import apus.entity.PhoneNumber;
import apus.entity.RealOrganization;
import apus.exception.DatabaseException;
import apus.exception.EntityNotFoundException;
import apus.persistence.OrganizationDAO;
import apus.persistence.proxy.PhoneNumberProxy;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author  Roman Dyatkovsky
 * @author  Maxim Vasilevsky
 * @since APUS v0.5
 */
public class JdbcOrganizationDAO extends JdbcSubscriberDAO<Organization> implements OrganizationDAO {

    @Override
    public List<Organization> findAll() {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        List<Organization> subscriberList = new ArrayList<>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select * from SUBSCRIBER where SUBSCRIBER_TYPE=1");

            while (rs.next()) {
                Organization subscriber;

                subscriber = new RealOrganization(rs.getString("SUBSCRIBER_NAME").trim(), rs.getString("ADDRESS").trim());
                subscriber.setBankingDetails(rs.getString("BANKING_DETAILS").trim());
                subscriber.setId(rs.getInt("ID_SUBSCRIBER"));

                rs2 = st.executeQuery("select ID_PHONE_NUMBER from PHONE_NUMBER where SUBSCRIBER_ID=" + rs.getInt("ID_SUBSCRIBER"));

                while (rs2.next()) {
                    PhoneNumber pn = new PhoneNumberProxy();
                    pn.setId(rs2.getInt("ID_PHONE_NUMBER"));
                    subscriber.addPhoneNumber(pn);
                }
                subscriberList.add(subscriber);
            }
        } catch (SQLException ex) {
            throw new DatabaseException("Can't read organization list", ex);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (rs2 != null) {
                    rs2.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (subscriberList.isEmpty()) {
            throw new EntityNotFoundException("Organizations not found");
        }
        return subscriberList;
    }
}
